package edu.njit.hz278.core.db;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.MethodUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * A basic {@link IQuery} implementation.
 * 
 * @author Rivers Zhang
 * 
 * @param <T>
 */
public class Query<T> implements IQuery<T> {

	/**
	 * Row mappers for a tuple and multiple tuples.
	 */
	protected final ResultSetHandler<T> h_single;
	protected final ResultSetHandler<List<T>> h_list;

	protected final String tableName;

	protected final String selectPrefix;
	protected final String listPrefix;
	protected final String insertPrefix;
	protected final String updatePrefix;
	protected final String deletePrefix;

	final Class<T> type;
	protected T dummyInstance;

	private static String normalize(String s) {
		char[] a = s.toCharArray();
		StringBuilder sb = new StringBuilder();

		sb.append(a[0] >= 'A' && a[0] <= 'Z' ? (char) (a[0] - 'A' + 'a') : a[0]);

		for (int i = 1, len = a.length; i < len; ++i) {
			char ch = a[i];
			if (ch >= 'A' && ch <= 'Z')
				sb.append('_').append((char) (ch - ('A' - 'a')));
			else
				sb.append(ch);
		}

		return sb.toString();
	}

	public Query(Class<T> type) {
		this.type = type;
		h_single = new BeanHandler<T>(type);
		h_list = new BeanListHandler<T>(type);
		tableName = normalize(type.getSimpleName());

		selectPrefix = String.format("SELECT * FROM %s WHERE ", tableName);
		listPrefix = String.format("SELECT * FROM %s ", tableName);
		insertPrefix = String.format("INSERT INTO %s ", tableName);
		updatePrefix = String.format("UPDATE %s SET ", tableName);
		deletePrefix = String.format("DELETE FROM %s ", tableName);

		try {
			dummyInstance = type.newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			log.error(type.getName(), e);
		}
	}

	private void logSQL(String s) {
		log.debug(logPrefix + s);
	}

	/**
	 * get/list methods
	 */

	/**
	 * Retrieve by id.
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public T get(long id) {
		logSQL(selectPrefix + String.format("id = %ld", id));
		try {
			return run.query(selectPrefix.concat("id = ?"), h_single, id);
		} catch (SQLException e) {
			log.error(e);
			return dummyInstance;
		}
	}

	private static String buildQuestionMarks(int len) {
		StringBuilder sb = new StringBuilder();
		sb.append('(');

		for (int i = 0; i < len - 1; ++i)
			sb.append("?, ");

		sb.append("?)");

		return sb.toString();
	}

	public List<T> get(long[] ids) {
		logSQL(selectPrefix + "id in (" + Arrays.toString(ids) + ")");
		String ques = buildQuestionMarks(ids.length);
		try {
			return run.query(selectPrefix.concat("id in ").concat(ques),
					h_list, ids);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}

	public T getByKeys(String[] keys, Object[] args) {
		String whereStat = StringUtils.join(keys, " = ? AND ") + " = ? ";
		logSQL(selectPrefix + String.format(whereStat, args));
		try {
			return run.query(selectPrefix.concat(whereStat), h_single, args);
		} catch (SQLException e) {
			log.error(e);
			return dummyInstance;
		}
	}

	public T getByKey(String key, Object arg) {
		logSQL(selectPrefix + String.format("%s = ?", key));
		try {
			return run.query(selectPrefix.concat(key).concat(" = ?"), h_single,
					arg);
		} catch (SQLException e) {
			log.error(e);
			return dummyInstance;
		}
	}

	public List<T> getByKey(String key, Object[] args) {
		logSQL(selectPrefix + key + "in (" + Arrays.toString(args) + ")");
		String ques = buildQuestionMarks(args.length);
		try {
			return run.query(
					selectPrefix.concat(key).concat(" in ").concat(ques),
					h_list, args);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}

	private List<T> __get(String[] fields, Object[] args, int ofs, int size) {
		String whereStat = StringUtils.join(fields, " = ? AND ") + " = ? ";
		logSQL(selectPrefix + String.format(whereStat, args)
				+ String.format(" LIMIT %d, %d", ofs, size));
		try {
			return run.query(
					selectPrefix.concat(whereStat).concat(" LIMIT ?, ?"),
					h_list, args, ofs, size);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}

	public List<T> get(String[] fields, Object[] args, int page, int size) {
		if (page < 1 || size < 1)
			return Collections.emptyList();
		return __get(fields, args, (page - 1) * size, size);
	}

	public List<T> get(String[] fields, Object[] args) {
		return __get(fields, args, 0, Integer.MAX_VALUE);
	}

	private List<T> __get(String field, Object arg, int ofs, int size) {
		logSQL(selectPrefix.concat(field).concat(" = ? ").concat(" LIMIT ?, ?"));
		try {
			return run.query(
					selectPrefix.concat(field).concat(" = ? ").concat(" LIMIT ?, ?"),
					h_list, arg, ofs, size);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}
	
	public List<T> get(String field, Object arg, int page, int size) {
		if (page < 1 || size < 1)
			return Collections.emptyList();
		return __get(field, arg, (page - 1) * size, size);
	}

	public List<T> get(String field, Object arg) {
		return __get(field, arg, 0, Integer.MAX_VALUE);
	}

	private List<T> __list(int ofs, int size) {
		logSQL(String.format(listPrefix + " LIMIT %d, %d", ofs, size));
		try {
			return run.query(listPrefix.concat(" LIMIT ?, ?"), h_list, ofs,
					size);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}

	public List<T> list() {
		return __list(0, Integer.MAX_VALUE);
	}

	public List<T> list(int page, int size) {
		if (page < 1 || size < 1)
			return Collections.emptyList();

		return __list((page - 1) * size, size);
	}

	private Map<String, Object> describe(T object) {
		Map<String, Object> desc = null;
		try {
			desc = PropertyUtils.describe(object);
			desc.remove("class");
			return desc;
		} catch (IllegalAccessException | InvocationTargetException
				| NoSuchMethodException e) {
			log.error(e);
			return Collections.emptyMap();
		}
	}

	/**
	 * Inserts an Object of type T.
	 * 
	 * @param object
	 * @return The auto-generated key. Only the 1st auto-generated key will be
	 *         returned.
	 * @throws SQLException
	 */
	public int insert(T object) {

		Map<String, Object> desc = describe(object);

		/**
		 * We create SQL statements and extract properties from the JavaBean.
		 */
		StringBuilder tableDesc = new StringBuilder();
		StringBuilder params = new StringBuilder();
		Object[] args = new Object[desc.size()];

		tableDesc.append('(');
		params.append('(');

		Iterator<Entry<String, Object>> iter = desc.entrySet().iterator();
		for (int i = 0; iter.hasNext(); ++i) {
			Entry<String, Object> entry = iter.next();
			tableDesc.append(entry.getKey());
			params.append('?');
			args[i] = entry.getValue();

			if (iter.hasNext()) {
				tableDesc.append(',');
				params.append(',');
			}
		}

		tableDesc.append(')');
		params.append(')');

		String sql = insertPrefix.concat(tableDesc.toString())
				.concat(" VALUES ").concat(params.toString());
		logSQL(sql);

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			conn = bds.getConnection();
			stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			run.fillStatement(stmt, args);
			stmt.executeUpdate();

			rs = stmt.getGeneratedKeys();
			if (rs.next())
				return rs.getInt(1);

		} catch (SQLException e) {
			log.error(e);
		} finally {

			try {
				DbUtils.close(rs);
				DbUtils.close(stmt);
				DbUtils.close(conn);
			} catch (SQLException e) {
				log.error(e);
			}

		}

		return 0;
	}

	public int update(T object, String[] whereAttrs) {

		Map<String, Object> desc = describe(object);

		Object[] args = new Object[desc.size()];

		StringBuilder whereBuilder = new StringBuilder();
		for (int i = 0, j = desc.size() - 1, len = whereAttrs.length; i != len; ++i, --j) {
			String attr = whereAttrs[i];
			whereBuilder.append(attr).append(" = ? ");
			args[j] = desc.remove(attr);
			if (i != len - 1)
				whereBuilder.append(" AND ");
		}

		StringBuilder setBuilder = new StringBuilder();
		Iterator<Entry<String, Object>> iter = desc.entrySet().iterator();
		for (int i = 0; iter.hasNext(); ++i) {
			Entry<String, Object> entry = iter.next();
			setBuilder.append(entry.getKey()).append(" = ? ");
			args[i] = entry.getValue();

			if (iter.hasNext())
				setBuilder.append(',');
		}

		String sql = updatePrefix.concat(setBuilder.toString())
				.concat(" WHERE ").concat(whereBuilder.toString());
		logSQL(sql);

		try {
			return run.update(sql, args);
		} catch (SQLException e) {
			log.error(e);
			return 0;
		}
	}

	public int update(T object, String[] setAttrs, String[] whereAttrs) {
		try {

			int lenSet = setAttrs.length, lenWhere = whereAttrs.length;
			Object[] args = new Object[lenSet + lenWhere];

			StringBuilder setBuilder = new StringBuilder();
			for (int i = 0; i != lenSet; ++i) {
				String attr = setAttrs[i];
				setBuilder.append(attr).append(" = ? ");
				args[i] = PropertyUtils.getProperty(object, attr);

				if (i != lenSet - 1)
					setBuilder.append(',');
			}

			StringBuilder whereBuilder = new StringBuilder();
			for (int i = 0; i != lenWhere; ++i) {
				String attr = whereAttrs[i];
				whereBuilder.append(attr).append(" = ? ");
				args[lenSet + i] = PropertyUtils.getProperty(object, attr);

				if (i != lenWhere - 1)
					whereBuilder.append(" AND ");
			}

			String sql = updatePrefix.concat(setBuilder.toString())
					.concat(" WHERE ").concat(whereBuilder.toString());
			logSQL(sql);

			return run.update(sql, args);

		} catch (IllegalAccessException | InvocationTargetException
				| NoSuchMethodException | SQLException e) {
			log.error(e);
			return 0;
		}
	}

	public int delete(T object, String[] whereAttrs) {
		try {

			int len = whereAttrs.length;
			Object[] args = new Object[len];

			StringBuilder whereBuilder = new StringBuilder();
			for (int i = 0; i != len; ++i) {
				String attr = whereAttrs[i];
				whereBuilder.append(attr).append(" = ? ");

				args[i] = PropertyUtils.getProperty(object, attr);

				if (i != len - 1)
					whereBuilder.append(" AND ");
			}

			String sql = deletePrefix.concat(" WHERE ").concat(
					whereBuilder.toString());
			logSQL(sql);
			return run.update(sql, args);

		} catch (IllegalAccessException | InvocationTargetException
				| NoSuchMethodException | SQLException e) {
			log.error(e);
			return 0;
		}
	}

	public int delete(String[] attrs, Object[] args) {
		assert attrs.length == args.length;
		StringBuilder whereBuilder = new StringBuilder();
		for (int i = 0, len = attrs.length; i != len; ++i) {
			whereBuilder.append(attrs[i]).append(" = ? ");
			if (i != len - 1)
				whereBuilder.append(" AND ");
		}
		String sql = deletePrefix.concat(" WHERE ").concat(
				whereBuilder.toString());
		logSQL(sql);
		try {
			return run.update(sql, args);
		} catch (SQLException e) {
			log.error(e);
			return 0;
		}
	}

	public int delete(String attr, Object arg) {
		return delete(new String[] { attr }, new Object[] { arg });
	}

	public List<T> getBySQL(String sql) {
		logSQL(sql);
		try {
			return run.query(sql, h_list);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}

	public T getOneBySQL(String sql) {
		logSQL(sql);
		try {
			return run.query(sql, h_single);
		} catch (SQLException e) {
			log.error(e);
			return dummyInstance;
		}
	}

	public int update(String sql) {
		logSQL(sql);
		try {
			return run.update(sql);
		} catch (SQLException e) {
			log.error(e);
			return 0;
		}
	}

	public List<T> getBySQL(String sql, Object... params) {
		logSQL(sql);
		try {
			return run.query(sql, h_list, params);
		} catch (SQLException e) {
			log.error(e);
			return Collections.emptyList();
		}
	}

	public T getOneBySQL(String sql, Object... params) {
		logSQL(sql);
		try {
			return run.query(sql, h_single, params);
		} catch (SQLException e) {
			log.error(e);
			return dummyInstance;
		}
	}

	public int update(String sql, Object... params) {
		logSQL(sql);
		try {
			return run.update(sql, params);
		} catch (SQLException e) {
			log.error(e);
			return 0;
		}
	}

	protected static final String logPrefix = "Quering database: ";
	private static final Logger log = LogManager.getLogger(Query.class);
	protected static final BasicDataSource bds = new BasicDataSource();
	protected static final QueryRunner run = new QueryRunner(bds);

	@SuppressWarnings("rawtypes")
	public static void init(Properties prop) {
		Enumeration e = prop.propertyNames();
		while (e.hasMoreElements()) {
			String key = (String) e.nextElement();
			String value = prop.getProperty(key);
			try {
				MethodUtils.invokeExactMethod(bds,
						"set" + StringUtils.capitalize(key), value);
			} catch (NoSuchMethodException | IllegalAccessException
					| InvocationTargetException ex) {
				log.error("Unknown configuration: " + key, ex);
			}
		}
	}

	public static void destroy() {
		try {
			bds.close();
		} catch (SQLException e) {
			log.error("Cannot close datasource.", e);
		}
	}

}
